EXCEL VBA 小技巧--資料夾分類
準備的檔案有
1.要分類的資料夾

2.開始準備巨集程式的按鍵

2.資料夾要分類的EXCEL檔案，從A3的位置開始放置

4.VBA巨集程式如下
Dim Check_Id As String
Dim Afile As String, Bfile As String
Dim i As Integer, FinalRow As Long
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
myFolder = "D:\UploadFile"
myName = Dir(myFolder, vbDirectory)
newFolder = "D:\NEW"
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
If Len(Dir("D:\NEW", vbDirectory)) = 0 Then
MkDir ("d:\NEW")
End If

For i = 4 To FinalRow '建立所有分類的子科目
    Stu_Class = "D:\NEW\" & Range("B" & i).Value
    If Len(Dir(Stu_Class, vbDirectory)) = 0 Then
       MkDir (Stu_Class)
    End If
Next i

For i = 4 To FinalRow '查對應的分類
    Stu_Id = "D:\NEW\" & Range("B" & i).Value & "\" & Range("A" & i)
    If Len(Dir(Stu_Id, vbDirectory)) = 0 Then
        MkDir (Stu_Id)
    End If
Next i


For i = 4 To FinalRow '開始搬移資料目錄
    Check_Id = Range("A" & i)
    Afile = myFolder & Range("A" & i)
    If Dir(Afile, vbDirectory) <> "" Then
        Bfile = newFolder & Range("B" & i) & "\" & Range("A" & i)
        fs.CopyFolder Afile, Bfile
    End If
    '資料夾下無檔案
Next i
MsgBox ("已分類完成")

5.結果如下圖

該分類下目錄下的檔案均一併搬移